package com.ctrip.corp.obt.shard.sql.mybatis.interceptor.inner;

import com.ctrip.corp.obt.generic.utils.CollectionUtils;
import com.ctrip.corp.obt.generic.utils.StringPool;
import com.ctrip.corp.obt.generic.utils.StringUtils;
import com.ctrip.corp.obt.shard.constants.DbType;
import com.ctrip.corp.obt.shard.sql.mybatis.interceptor.dialect.DialectFactory;
import com.ctrip.corp.obt.shard.sql.mybatis.interceptor.dialect.IDialect;
import com.ctrip.corp.obt.shard.sql.parser.AbstractSqlParserSupport;
import com.ctrip.corp.obt.shard.utils.JdbcUtils;
import com.ctrip.corp.obt.shard.utils.PluginUtils;
import com.google.common.collect.Lists;
import lombok.ToString;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.BinaryExpression;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.Function;
import net.sf.jsqlparser.expression.MySQLGroupConcat;
import net.sf.jsqlparser.expression.NotExpression;
import net.sf.jsqlparser.expression.Parenthesis;
import net.sf.jsqlparser.expression.operators.relational.ComparisonOperator;
import net.sf.jsqlparser.expression.operators.relational.ExistsExpression;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.expression.operators.relational.IsNullExpression;
import net.sf.jsqlparser.expression.operators.relational.ItemsList;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.AllColumns;
import net.sf.jsqlparser.statement.select.AllTableColumns;
import net.sf.jsqlparser.statement.select.Distinct;
import net.sf.jsqlparser.statement.select.FromItem;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.LateralSubSelect;
import net.sf.jsqlparser.statement.select.OrderByElement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SelectExpressionItem;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.statement.select.SetOperationList;
import net.sf.jsqlparser.statement.select.SubJoin;
import net.sf.jsqlparser.statement.select.SubSelect;
import net.sf.jsqlparser.statement.select.WithItem;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.statement.update.UpdateSet;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Set;

/**
 * @author x.shen
 * @desc 信创数据库兼容性拦截器
 * @since 2023/10/25
 */
@Slf4j
@ToString(callSuper = true)
public class DatabaseCompatibilityInnerPlugin extends AbstractSqlParserSupport implements InnerPlugin {

    public static final Set<DbType> DB_TYPE_SET = new HashSet<>(Lists.newArrayList(DbType.DM, DbType.KINGBASE));

    private IDialect getDialect(Connection connection) {
        return DialectFactory.getDialect(JdbcUtils.getDbType(connection));
    }

    /**
     * 根据arch平台配置的插件，来判断是否启用
     *
     * @return
     * @throws SQLException
     */
    @Override
    public boolean skipPluginQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds,
        ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        // 当选择了dm、kingbase等数据库时，就需要默认开启这类插件，即使插件没有配置
        DbType dbType = JdbcUtils.getDbType(executor.getTransaction().getConnection());
        return !DB_TYPE_SET.contains(dbType);
    }

    /**
     * select前置操作，
     *
     * @param executor Executor(可能是代理对象)
     * @param ms MappedStatement
     * @param parameter parameter
     * @param rowBounds rowBounds
     * @param resultHandler resultHandler
     * @param boundSql boundSql
     * @throws SQLException
     */
    @Override
    public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds,
        ResultHandler resultHandler, BoundSql boundSql) throws SQLException {

        PluginUtils.DSBoundSql dsBoundSql = PluginUtils.dsBoundSql(boundSql);

        // 获取对应数据库处理方言
        IDialect iDialect = getDialect(executor.getTransaction().getConnection());
        dsBoundSql.setIDialect(iDialect);

        // 前置全局处理
        String sql = iDialect.preHandleSql(dsBoundSql.sql());

        // 处理其他细节
        dsBoundSql.sql(parserSingle(sql, dsBoundSql));
        log.info("DatabaseCompatibilityInnerPlugin finished: {}", dsBoundSql.sql());
    }

    /**
     * insert、update、delete执行前操作
     * 
     * @param sh StatementHandler(可能是代理对象)
     * @param connection Connection
     * @param transactionTimeout transactionTimeout
     */
    @Override
    public void beforePrepare(StatementHandler sh, Connection connection, Integer transactionTimeout) {
        // 当选择了dm、kingbase等数据库时，就需要默认开启这类插件，即使插件没有配置
        if (!DB_TYPE_SET.contains(JdbcUtils.getDbType(connection))) {
            return;
        }
        PluginUtils.DSStatementHandler dsSh = PluginUtils.dsStatementHandler(sh);
        MappedStatement ms = dsSh.mappedStatement();
        SqlCommandType sct = ms.getSqlCommandType();
        PluginUtils.DSBoundSql dsBoundSql = dsSh.dsBoundSql();

        if (sct == SqlCommandType.INSERT || sct == SqlCommandType.UPDATE || sct == SqlCommandType.DELETE) {
            // 获取对应数据库处理方言
            IDialect iDialect = getDialect(connection);
            dsBoundSql.setIDialect(iDialect);

            // 前置全局处理
            String sql = iDialect.preHandleSql(dsBoundSql.sql());

            dsBoundSql.sql(parserMulti(sql, dsBoundSql));
        }
        log.info("DatabaseCompatibilityInnerPlugin finished: {}", dsBoundSql.sql());
    }

    @Override
    public int order() {
        return -200;
    }

    /**
     *
     * @param select
     * @param index
     * @param sql
     * @param obj --- dsBoundSql 向下传递，其中包含了 iDialect
     */
    @Override
    protected void processSelect(Select select, int index, String sql, Object obj) {
        handleSelectBody(select.getSelectBody(), (PluginUtils.DSBoundSql)obj);
        List<WithItem> withItemsList = select.getWithItemsList();
        if (!CollectionUtils.isEmpty(withItemsList)) {
            withItemsList.forEach(withItem -> {
                handleSelectBody(withItem, (PluginUtils.DSBoundSql)obj);
            });
        }
    }

    /**
     * 处理select相关兼容语句
     * 1.
     *
     * @param selectBody
     * @param dsBoundSql
     */
    private void handleSelectBody(SelectBody selectBody, PluginUtils.DSBoundSql dsBoundSql) {
        if (selectBody == null) {
            return;
        }
        if (dsBoundSql.getIDialect() == null) {
            return;
        }
        if (selectBody instanceof PlainSelect) {
            handlePlainSelect((PlainSelect)selectBody, dsBoundSql);
        } else if (selectBody instanceof WithItem) {
            WithItem withItem = (WithItem)selectBody;
            handleSelectBody(withItem.getSubSelect().getSelectBody(), dsBoundSql);
        } else {
            List<SelectBody> selectBodys = ((SetOperationList)selectBody).getSelects();
            if (CollectionUtils.isNotEmpty(selectBodys)) {
                selectBodys.forEach(sb -> handleSelectBody(sb, dsBoundSql));
            }
        }
    }

    /**
     * 按需处理select语句中的部分细节
     * 1. 别名部分
     * 
     * @param plainSelect
     * @param dsBoundSql
     */
    private void handlePlainSelect(PlainSelect plainSelect, PluginUtils.DSBoundSql dsBoundSql) {

        // selectItem 是否存在子查询 + 函数处理
        List<SelectItem> selectItemsList = plainSelect.getSelectItems();
        if (CollectionUtils.isNotEmpty(selectItemsList)) {
            selectItemsList.forEach(selectItem -> handleSelectItem(selectItem, dsBoundSql));
        }

        // fromItem 处理 as 别名、from (子查询) as temp_table
        FromItem fromItem = plainSelect.getFromItem();
        handleFromItem(fromItem, dsBoundSql);

        // where 处理非动态sql的字段类型转化，函数映射
        Expression where = plainSelect.getWhere();
        handleWhere(where, dsBoundSql);

        // join 处理
        List<Join> joinList = plainSelect.getJoins();
        if (CollectionUtils.isNotEmpty(joinList)) {
            handleJoin(joinList, dsBoundSql);
        }

        // order by 与 distinct 处理 ===> order by 后的列必须出现在distinct后
        List<OrderByElement> orderByElementList = plainSelect.getOrderByElements();
        Distinct distinct = plainSelect.getDistinct();
        if (Objects.nonNull(distinct) && CollectionUtils.isNotEmpty(orderByElementList)) {
            handleOrderByWithDistinct(orderByElementList, selectItemsList, dsBoundSql);
        }
    }

    /**
     * 处理查询中的selectItem
     *
     * @param selectItem
     * @param dsBoundSql
     */
    private void handleSelectItem(SelectItem selectItem, PluginUtils.DSBoundSql dsBoundSql) {
        // 只处理如下一种情况，对于 * 或者 table.* 忽略不处理
        if (selectItem instanceof AllColumns || selectItem instanceof AllTableColumns) {
            return;
        }

        if (selectItem instanceof SelectExpressionItem) {
            SelectExpressionItem selectExpressionItem = (SelectExpressionItem)selectItem;

            // 处理字段别名，将别名中所有的引号去掉，变成无引号形式
            dsBoundSql.getIDialect().handleAsAlias(selectExpressionItem.getAlias());
            // 处理关键字
            dsBoundSql.getIDialect().handleKeyword(selectExpressionItem.getExpression());

            Expression expression = selectExpressionItem.getExpression();
            // 子查询
            if (expression instanceof SubSelect) {
                handleSelectBody(((SubSelect)expression).getSelectBody(), dsBoundSql);
            } else if (expression instanceof Function) {
                // 处理函数
                handleFunction((Function)expression, dsBoundSql);
            }else if (expression instanceof MySQLGroupConcat){
                // 处理group_concat 这也是mysql函数，但并不是function类型
                // 要将这个expression完全替换，不能立刻转换类型
                selectExpressionItem.setExpression(handleGroupConcat(expression, dsBoundSql));
            }
        }
    }

    /**
     * 处理待兼容函数
     *
     * @param expression
     * @param dsBoundSql
     */
    private void handleFunction(Function expression, PluginUtils.DSBoundSql dsBoundSql) {
        IDialect iDialect = dsBoundSql.getIDialect();
        iDialect.handleFunction(expression);
    }

    /**
     * 处理group_concat函数
     *
     * @param expression
     * @param dsBoundSql
     */
    private Expression handleGroupConcat(Expression expression, PluginUtils.DSBoundSql dsBoundSql) {
        IDialect iDialect = dsBoundSql.getIDialect();
        return iDialect.handleGroupConcat(expression);
    }

    /**
     * 处理from后函数，可能是表，也可能是子查询
     *
     * @param fromItem
     * @param dsBoundSql
     */
    private Map<String, Table> handleFromItem(FromItem fromItem, PluginUtils.DSBoundSql dsBoundSql) {
        if (Objects.isNull(fromItem)) {
            return Collections.emptyMap();
        }

        IDialect iDialect = dsBoundSql.getIDialect();
        // 统一处理其别名，不论是否是子查询
        iDialect.handleAsAlias(fromItem.getAlias());

        // 获得sql中的表名，用以后续where中获取对应字段类型
        Map<String, Table> result = new HashMap<>();
        if (fromItem instanceof Table) {
            Table table = (Table)fromItem;
            if (StringUtils.isNotBlank(table.getName())) {
                result.putIfAbsent(table.getName(), table);
            }
            if (Objects.nonNull(table.getAlias()) && StringUtils.isNotBlank(table.getAlias().getName())) {
                result.putIfAbsent(table.getAlias().getName(), table);
            }
        }

        // 处理from中包含join语句
        // from ((tbl1 LEFT JOIN tbl2 ON ((tbl1.id = tbl2.id))) LEFT JOIN tbl3 ON ((tbl1.id = tbl3.id)))
        if (fromItem instanceof SubJoin) {
            SubJoin subJoin = (SubJoin)fromItem;
            // 处理join右表 --- 表 + 子查询
            // handleJoins(subJoin.getJoinList());
            // 处理join左表 --- 表 + 子查询
            result.putAll(handleFromItem(subJoin.getLeft(), dsBoundSql));
        } else if (fromItem instanceof SubSelect) {
            // 子查询
            handleSelectBody(((SubSelect)fromItem).getSelectBody(), dsBoundSql);
        } else if (fromItem instanceof LateralSubSelect) {
            // FROM tableA a, LATERAL (SELECT * FROM tableB b WHERE b.column = a.column) AS subquery
            // LATERAL 关键字起到了和join一样的作用，但是其发生在 from 语句后
            LateralSubSelect lateralSubSelect = (LateralSubSelect)fromItem;
            if (lateralSubSelect.getSubSelect() != null) {
                handleSelectBody(lateralSubSelect.getSubSelect().getSelectBody(), dsBoundSql);
            }
        }
        return result;
    }

    /**
     * 处理 where 语句
     * where 语句按照顺序解析表达式，左表达式存放一个单独的表达式内容，右表达式存放剩余内容
     * 
     * @param where
     * @param dsBoundSql
     */
    private void handleWhere(Expression where, PluginUtils.DSBoundSql dsBoundSql) {
        if (Objects.isNull(where)) {
            return;
        }

        // where 最小元素之一，目前只处理 =
        if (where instanceof ComparisonOperator) {
            // = > != < 等元素，针对 EqualTo 和 NotEqualsTo
            ComparisonOperator equalsTo = (ComparisonOperator)where;
            // 如果是？就直接返回
            if (StringPool.QUESTION_MARK.equals(equalsTo.getRightExpression().toString())) {
                return;
            }

            // 处理可能为待兼容类型的字段
            Expression leftExpression = equalsTo.getLeftExpression();
            Expression rightExpression = equalsTo.getRightExpression();
            // ASTNodeAccessImpl 包含 column 和 LongValue 等 id = 495 左边为column 右边为 LongValue
            // is_delete = true 时，true instanceof column，is_delete = 0 时，0 instanceof LongValue
            if (leftExpression instanceof Column && rightExpression instanceof Function) {
                // where函数中包含函数，
                handleFunction((Function)rightExpression, dsBoundSql);
            } else if (leftExpression instanceof Column) {
                // 右侧为非函数类型
                IDialect iDialect = dsBoundSql.getIDialect();
                iDialect.handleColumnType((Column)leftExpression, rightExpression);
            } else if (leftExpression instanceof Function) {
                // 左表达式为函数
                handleFunction((Function)leftExpression, dsBoundSql);
            }
            return;
        }

        // 处理其他的 where 复杂类型表达式
        handleWhereItem(where, dsBoundSql);
    }

    /**
     * 处理其他的 where 复杂类型表达式，递归将其转换为最小执行单元，
     *
     * @param where
     * @param dsBoundSql
     */
    private void handleWhereItem(Expression where, PluginUtils.DSBoundSql dsBoundSql) {
        if (where instanceof BinaryExpression) {
            // 比较符号 , and , or , 等等
            BinaryExpression expression = (BinaryExpression)where;
            handleWhere(expression.getLeftExpression(), dsBoundSql);
            handleWhere(expression.getRightExpression(), dsBoundSql);
        } else if (where instanceof InExpression) {
            // in
            // is_delete IN (SELECT is_delete FROM mb_org_project_info WHERE id = 256)
            InExpression expression = (InExpression)where;
            // 以in为分界线，右表达式 (SELECT is_delete FROM mb_org_project_info WHERE id = 256)
            ItemsList itemsList = expression.getRightItemsList();
            // SubSelect 表示子查询类型
            if (itemsList instanceof SubSelect) {
                handleSelectBody(((SubSelect)itemsList).getSelectBody(), dsBoundSql);
            }
        } else if (where instanceof ExistsExpression) {
            // exists
            ExistsExpression expression = (ExistsExpression)where;
            handleWhere(expression.getRightExpression(), dsBoundSql);
        } else if (where instanceof NotExpression) {
            // not exists
            NotExpression expression = (NotExpression)where;
            handleWhere(expression.getExpression(), dsBoundSql);
        } else if (where instanceof Parenthesis) {
            Parenthesis expression = (Parenthesis)where;
            handleWhere(expression.getExpression(), dsBoundSql);
        } else if (where instanceof IsNullExpression) {
            // is not null
            IsNullExpression expression = (IsNullExpression)where;
            if (expression.getLeftExpression() instanceof Function) {
                handleFunction((Function)expression.getLeftExpression(), dsBoundSql);
            }
        }
    }

    /**
     * 处理join
     *
     * @param joinList
     * @param dsBoundSql
     */
    private void handleJoin(List<Join> joinList, PluginUtils.DSBoundSql dsBoundSql) {
        for (Join join : joinList) {
            // 处理 on 表达式
            FromItem fromItem = join.getRightItem();
            handleFromItem(fromItem, dsBoundSql);
        }
    }

    /**
     * SELECT DISTINCT a.uid, b.employee_type,
     * FROM mb_user_org_relation a
     * JOIN mb_org_employee_info b
     * ON a.uid = b.uid
     * WHERE a.delete_time = '1970-01-01 08:00:00'
     * ORDER BY FIELD(a."status", 2) DESC, a.id DESC LIMIT 1
     * a.id 需要存在select后，包括被函数笼罩的a.status
     * 该修改不论是否需要，均强硬添加，即使存在重复字段的情况
     *
     * @param orderByElementList
     * @param selectItemsList
     * @param dsBoundSql
     */
    private void handleOrderByWithDistinct(List<OrderByElement> orderByElementList, List<SelectItem> selectItemsList,
        PluginUtils.DSBoundSql dsBoundSql) {
        // select * 不处理，但若是存在 a.* 这种，依旧需要处理
        if (selectItemsList.size() == 1 && selectItemsList.get(0) instanceof AllColumns) {
            return;
        }

        List<Column> needAddColumnList = new ArrayList<>();

        // 解析order by
        for (OrderByElement orderByElement : orderByElementList) {
            // 获取order by 表达式
            Expression expression = orderByElement.getExpression();
            if (expression instanceof Function) {
                Function function = (Function)expression;
                // 处理function中的字段
                ExpressionList parameters = function.getParameters();
                if (Objects.nonNull(parameters)) {
                    parameters.getExpressions().forEach(exp -> {
                        if (exp instanceof Column) {
                            needAddColumnList.add((Column)exp);
                        }
                    });
                }
            } else if (expression instanceof Column) {
                needAddColumnList.add((Column)expression);
            }

        }
        dsBoundSql.getIDialect().handleOrderByWithDistinct(needAddColumnList, selectItemsList);
    }

    /**
     * 去除插入主键时，对应值为null的情况
     *
     * @param insert
     * @param index
     * @param sql
     * @param obj
     */
    @Override
    protected void processInsert(Insert insert, int index, String sql, Object obj) {
        if (!(obj instanceof PluginUtils.DSBoundSql)) {
            return;
        }
        // insert语句后紧接value(?,?,?)，因此没必要进行字段类型转换等操作
        // 目前，insert只处理主键相关问题
        PluginUtils.DSBoundSql boundSql = (PluginUtils.DSBoundSql)obj;
        // 兼容insert
        boundSql.getIDialect().handleInsertPrimaryKey(insert, boundSql);
    }

    @Override
    protected void processUpdate(Update update, int index, String sql, Object obj) {
        if (!(obj instanceof PluginUtils.DSBoundSql)) {
            return;
        }

        PluginUtils.DSBoundSql dsBoundSql = (PluginUtils.DSBoundSql)obj;

        // 处理 updateSet
        ArrayList<UpdateSet> updateSetsList = update.getUpdateSets();
        handleUpdateSet(updateSetsList, dsBoundSql);

        // fromItem 处理 as 别名、from (子查询) as temp_table
        FromItem fromItem = update.getFromItem();
        handleFromItem(fromItem, dsBoundSql);

        // where 处理非动态sql的字段类型转化，函数映射
        Expression where = update.getWhere();
        handleWhere(where, dsBoundSql);

        // join 处理
        List<Join> joinList = update.getJoins();
        if (CollectionUtils.isNotEmpty(joinList)) {
            handleJoin(joinList, dsBoundSql);
        }
    }

    /**
     * 处理update中的set字段，确保类型转换正常
     *
     * @param updateSetsList
     * @param dsBoundSql
     */
    private void handleUpdateSet(ArrayList<UpdateSet> updateSetsList, PluginUtils.DSBoundSql dsBoundSql) {

        Iterator<UpdateSet> iterator = updateSetsList.iterator();
        while (iterator.hasNext()) {
            UpdateSet updateSet = iterator.next();
            ArrayList<Expression> expressions = updateSet.getExpressions();
            ArrayList<Column> columns = updateSet.getColumns();

            // 决定是否剔除主键id
            boolean removePrimaryKey = false;

            for (int i = 0; i < expressions.size(); i++) {
                Expression expression = expressions.get(i);
                // 剔除 ?
                if (StringPool.QUESTION_MARK.equals(expression.toString())) {
                    continue;
                }

                // 处理 set column = xxx （非#{}）
                Column column = columns.get(i);
                IDialect iDialect = dsBoundSql.getIDialect();
                iDialect.handleColumnType(column, expression);

                // 处理update的主键问题
                removePrimaryKey = iDialect.handleUpdatePrimaryKey(column, expression);
            }
            if (removePrimaryKey) {
                iterator.remove();
            }
        }
    }

    @Override
    protected void processDelete(Delete delete, int index, String sql, Object obj) {
        if (!(obj instanceof PluginUtils.DSBoundSql)) {
            return;
        }
        PluginUtils.DSBoundSql boundSql = (PluginUtils.DSBoundSql)obj;
        // 兼容delete
        boundSql.getIDialect().handleDelete(delete, boundSql);
    }
}
